package com.springboot.demo.mapper;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.springboot.demo.entity.*;
import com.springboot.demo.entity.validate.TableBasicInfoGroup;
import io.swagger.annotations.ApiModelProperty;
import org.apache.ibatis.annotations.*;
import org.hibernate.validator.constraints.Length;
import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author wubc683
 * @desc
 * @date 2021-11-22 17:18
 */
public interface TableMapper {
    @Select("select id,ownerId,ownerName,createDate,type,title,dataTableType,dimension,compare,isExportAllowed,value,valueType" +
            " from web_table where id in (select tableId from web_panel_table " +
            "where panelId = #{pid})")
    List<Table> getPanelTableByPanelId(Long pid);

    @Select("select ownerId from web_table where id = #{tableId}")
    Long getTableOwnerIdByTableId(Long tableId);

    @Results({
            @Result(column = "id", property = "id"), //@Result：封装映射关系的子注解
            //column 属性：查询出的表中字段名称
            //property 属性：实体对象中的属性名称
            @Result(property = "filters",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型,把该列做参数传给查询语句
                    column = "id",
                    many = @Many(select = "com.springboot.demo.mapper.TableMapper.getTableFilter")
            )//many 属性：一对多查询固定属性
    })
    @Select("<script> select t.id as id,t.type as type,t.title as title,t.createDate as createDate,t.dataTableType " +
            "as dataTableType,t.dimension as dimension,t.compare as compare,t.value as value,t.valueType as " +
            "valueType,t" + ".isExportAllowed " + "as " + "isExportAllowed,u" +
            ".username as ownerName ,u.id as ownerId from " +
            "web_table t left join web_user u on t.ownerId = u.id where ownerId = #{uid} "+
            "<if test='search !=null and search !=\"\"'> and t.title like concat('%', #{search},'%') </if> " +
            "<if test='type !=null '> and type = #{type} </if>" +
            "<if test='sort !=null and sort != \"\"'> order by " + "#{sort} </if>" +
            "</script>")
    List<Table> getAllTables(Long uid, String search, Integer type, String sort, Date startDate, Date endDate);

    @Select("select t.id,type,title,ownerId,u.username as ownerName ,t.createDate,dataTableType,dimension,compare," +
            "value,valueType," + "isExportAllowed" +
            " from web_table t left join web_user u on t.ownerId = u.id where t.id = #{tid} ")
    Table getTableById(Long tid);

    @Results({
            @Result(column = "id", property = "id"), //@Result：封装映射关系的子注解
            //column 属性：查询出的表中字段名称
            //property 属性：实体对象中的属性名称
            @Result(property = "filterData",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型,把该列做参数传给查询语句
                    column = "id",
                    many = @Many(select = "com.springboot.demo.mapper.TableMapper.getTableFilterData")
            )//many 属性：一对多查询固定属性
    })
    @Select("select id,tableId,filterField,filterMode,filterDataType from web_table_filter where tableId " +
            "= " + "#{tid}")
    List<TableFilter> getTableFilter(Long tid);

    @Select("select filterData from web_table_filter_data where filterId = #{filterId}")
    List<String> getTableFilterData(Long filterId);

    @Select("select id from web_table where ownerId = #{uid} and title = #{title}")
    Long getTableIdByOwnerIdAndTitle(Long uid, String title);

    @Insert("insert into web_table(type,title,ownerId,createDate,dataTableType,dimension,compare,value,valueType," +
            "isExportAllowed) " +
            "values (#{type},#{title},#{ownerId},#{createDate},#{dataTableType},#{dimension},#{compare},#{value}," +
            "#{valueType},#{isExportAllowed})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveTable(Table table);

    @Insert("<script> insert into web_table_filter(tableId,filterField,filterMode,filterDataType) " +
            "values <foreach item='item' collection='filters' separator=','>" +
            "(#{tid},#{item.filterField},#{item.filterMode},#{item.filterDataType}) </foreach> " +
            "</script>")
    @Options(useGeneratedKeys = true, keyProperty = "filters.id", keyColumn = "filters.id")
    void saveTableFilter(Long tid, List<TableFilter> filters);

    @Update("update web_table set type =#{type},title=#{title},dataTableType=#{dataTableType},dimension=#{dimension}," +
            "compare=#{compare},value=#{value},valueType=#{valueType} ,isExportAllowed=#{isExportAllowed} where id = " +
            "#{id}")
    void updateTable(Table table);

    @Delete("delete from web_table_filter where tableId = #{tableId}")
    void deleteTableFilter(Long tableId);

    @Select("select id from web_table where ownerId = #{uid}")
    List<Long> getTableIdByUid(Long uid);

    @Delete("<script> delete from web_table where id in <foreach item = 'item' collection='tableIds' open='(' " +
            "close=')' separator=','> #{item} </foreach> </script>")
    void deleteTableInBatches(List<Long> tableIds);

    @Delete("<script> delete from web_table_filter where tableId in <foreach item = 'item' collection='tableIds' open='(' " +
            " close=')'  separator=','>  #{item} </foreach> </script>")
    void deleteTableFilterInBatches(List<Long> tableIds);

    @SelectProvider(type = SqlMapper.class, method = "getIndexTableDataSql")
    Double getIndexTableData(Table table, List<TableFilterValue> tableFilterValueList);

    @SelectProvider(type = SqlMapper.class, method = "getTableTableCompareFieldSql")
    List<String> getTableTableCompareFieldSql(Table table, List<TableFilterValue> tableFilterValueList);

    @SelectProvider(type = SqlMapper.class, method = "getTableTableDataSql")
    List<Map<String, Object>> getTableTableData(Table table,
                                                List<String> tableCompareField,
                                                List<TableFilterValue> tableFilterValueList, String sort);

    @SelectProvider(type = SqlMapper.class, method = "getTablePieDataSql")
    List<Map<String, Object>> getTablePieData(Table table, List<TableFilterValue> tableFilterValueList);

    @Delete("<script> delete from web_panel_table where tableId in <foreach item = 'item' " +
            "collection='tableIds' open='(' " +
            " close=')'  separator=','>  #{item} </foreach> </script>")
    void deletePanelTableInBatches(List<Long> tableIds);

    @Select("<script> select distinct username from web_user where (id = #{uid} or parentId = #{uid}) and deleteFlag " +
            "= '' " + "<if test='search !=null and search != \"\"'> "+
            "and " + "username " +
            "like " + "concat('%'," + " " + "#{search},'%') </if> </script>")
    List<String> getTableFilterFieldUsername(Long uid, String search);

    @Select("<script> select distinct phone from web_user where (id = #{uid} or parentId = #{uid}) and deleteFlag = " +
            "'' and phone is not null " + "<if test='search !=null and search != \"\"'> "+
            "and " + "phone like " + "concat('%'," + " " + "#{search},'%') </if> </script>")
    List<String> getTableFilterFieldPhone(Long uid, String search);

    @Select("<script> select distinct r.name from web_role r inner join web_user u on r.id = u.rid where (u.id = " +
            "#{uid} or u" + ".parentId =" +
            " " + "#{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and r.name like concat('%'," + " " + "#{search},'%') </if> </script>")
    List<String> getTableFilterFieldRole(Long uid, String search);

    @Select("<script> select distinct d.deviceName from web_device d inner join" +
            " web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId inner join web_user u on u.id" +
            " = udc.userId " + " where (u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and d.deviceName" +
            " like " + " concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldDeviceName(Long uid, String search);

    @Select("<script> select distinct dc.name from web_device d inner join web_device_cluster dc on d.deviceClusterId" +
            " = dc.id " +
            "inner " + "join" +
            " web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId inner join web_user u on u.id" +
            " = udc.userId " + " where (u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and dc.name like " + "concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldDeviceCluster(Long uid, String search);

    @Select("<script> select distinct d.province from web_device d inner join" +
            " web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId inner join web_user u on u.id" +
            " = udc.userId " + " where (u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and d.province " + "like " + "concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldProvince(Long uid, String search);

    @Select("<script> select distinct d.city from web_device d inner join" +
            " web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId inner join web_user u on u.id" +
            " = udc.userId " + " where (u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and d.city " +
            "like " + "concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldCity(Long uid, String search);

    @Select("<script> select distinct d.region from web_device d inner join" +
            " web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId inner join web_user u on u.id" +
            " = udc.userId " + " where (u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and d.region " +
            "like " + "concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldRegion(Long uid, String search);

    @Select("<script> select distinct s.name from web_scene s inner join web_user u on u.id = s.ownerId  where " +
            "(u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            " and s.name like concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldSceneName(Long uid, String search);

    @Select("<script> select distinct sg.name from web_scene s inner join web_scene_group sg on s.sceneGroupId = sg" +
            ".id inner " + "join " +
            "web_user u on u.id = s.ownerId  where " +
            "(u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = ''" +
            "<if test='search !=null and search != \"\"'> "+
            " and sg.name like concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldSceneGroupName(Long uid, String search);

    @Select("<script> select distinct v.name from web_variable v inner join web_scene s on s.id = v.sceneId inner " +
            "join " +
            "web_user u on u.id = " + "s.ownerId  where " +
            "(u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = ''" +
            " <if test='search !=null and search != \"\"'> "+
            " and v.name like concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldVariableName(Long uid, String search);

    @Select("<script> select distinct aa.alarmText from web_analog_alarm aa inner join web_scene s on s.id = aa" +
            ".sceneId inner " +
            "join web_user u on u.id = s.ownerId  where " +
            "(u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            "and aa.alarmText like concat('%',#{search}," + "'%') </if>" +
            " union "+
            "select distinct da.alarmText from web_discrete_alarm da inner join web_scene s on s.id = da.sceneId " +
            "inner " + "join web_user u on u.id = s.ownerId  where " +
            "(u.id = #{uid} or u.parentId = #{uid}) and u.deleteFlag = '' " +
            "<if test='search !=null and search != \"\"'> "+
            " and da.alarmText like concat('%',#{search},'%') </if> </script>")
    List<String> getTableFilterFieldAlarmContent(Long uid, String search);

    @Insert("<script> insert into web_table_filter_data(filterId,filterData) values " +
            "<foreach item='item1' " + "collection='filters' separator=','>" +
            "<foreach item='item2' collection='item1.filterData' separator=','> " +
            "(#{item1.id},#{item2})" +
            "</foreach></foreach></script>")
    void saveTableFilterData(List<TableFilter> filters);
}
